package com.css.dzj.dzjap.modules.export.service;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @DESC excel 读取工具类
 */
public class ExcelUtil {
    private static  int totalRows = 0;//总行数
    private static  int totalCells = 0;//总列数
    private static  String errorInfo ;//错误信息

    public ExcelUtil() {
    }

    public static int getTotalRows() {
        return totalRows;
    }

    public static void setTotalRows(int totalRows) {
        ExcelUtil.totalRows = totalRows;
    }

    public static int getTotalCells() {
        return totalCells;
    }

    public static void setTotalCells(int totalCells) {
        ExcelUtil.totalCells = totalCells;
    }

    public static String getErrorInfo() {
        return errorInfo;
    }

    public static void setErrorInfo(String errorInfo) {
        ExcelUtil.errorInfo = errorInfo;
    }

    /**
     * 根据流读取Excel文件
     *
     * inputStream
     * isExcel2003
     *
     */
    public List<List<String>> read(InputStream inputStream,boolean isExcel2003) throws IOException{
        List<List<String>> dataLst = null;

        /**
         *根据版本选择创建Workbook的方式
         */
        Workbook wb =null;

        if(isExcel2003){
            wb = new HSSFWorkbook(inputStream);

        }else{
            wb = new XSSFWorkbook(inputStream);
        }
        dataLst = readData(wb);

        return  dataLst;
    }

    /**
     * 读取数据
     * wb
     *
     *
     */
    private  List<List<String>> readData(Workbook wb){

        List<List<String>> datalst = new ArrayList<List<String>>();

        /**
         * 得到第一个shell
         */
        Sheet sheet = wb.getSheetAt(0);

        /**
         * 得到Excel的行数
         */
        totalRows = sheet.getPhysicalNumberOfRows();
        /**
         * 得到Excel的列数
         */
        if(totalRows >=1 && sheet.getRow(0) != null){
            totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
        }

        /***
         * 循环Excel的行
         */
        for(int r =0;r <totalRows;r++){
            Row row =sheet.getRow(r);
            if(row == null){
                continue;
            }

            List<String> rowLst = new ArrayList<String>();
            /**
             *循环Excel的列
             */
            for(int c =0;c< getTotalCells();c++){

                Cell cell = row.getCell(c);
                String cellValue = "";

                if(cell !=null){
                    //以下是判断数据的类型
                    switch (cell.getCellType()){
                        case HSSFCell
                                .CELL_TYPE_NUMERIC://数字
                            cellValue = cell.getNumericCellValue() +"";
                            break;
                        case HSSFCell.CELL_TYPE_STRING: //字符串
                            cellValue = cell.getStringCellValue() ;
                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
                            cellValue = cell.getBooleanCellValue() +"" ;
                            break;
                        case HSSFCell.CELL_TYPE_FORMULA://公式
                           cellValue = cell.getCellFormula() +"";
                           break;
                        case HSSFCell.CELL_TYPE_BLANK://空值
                           cellValue = "";
                           break;
                        case HSSFCell.CELL_TYPE_ERROR://故障
                           cellValue = "非法字符";
                           break;
                        default:
                            cellValue = "位置类型";
                            break;

                    }
                }
                rowLst.add(cellValue);
            }
            /**
             * 保存第R行的第c列
             */
            datalst.add(rowLst);
        }
        return datalst;
    }

    /**
     * 根据Excel表格中的数据判断类型得到值
     */

    public static  String getCellValue(Cell cell){
        String cellValue = "";
        if(null !=cell){
            //以下是判断数据的类型
            switch (cell.getCellType()){
                case HSSFCell.CELL_TYPE_NUMERIC ://数字
                    if(org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)){
                       Date theDate = cell.getDateCellValue();
                        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
                        cellValue = dff.format(theDate);
                    }else{
                        DecimalFormat df = new DecimalFormat("0");
                        cellValue = df.format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: //字符串
                    cellValue = cell.getStringCellValue() ;
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: //Boolean
                    cellValue = cell.getBooleanCellValue() +"" ;
                    break;
                case HSSFCell.CELL_TYPE_FORMULA://公式
                    cellValue = cell.getCellFormula() +"";
                    break;
                case HSSFCell.CELL_TYPE_BLANK://空值
                    cellValue = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR://故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "位置类型";
                    break;
            }
        }
        return cellValue;
    }

    /**
     * 根据路径或文件选择excel版本
     */
    public static  Workbook chooseWorkbook(String filePathOrName,InputStream in) throws IOException{

        /**
         * 根据版本选择创建WorkBook的方式
         */
        Workbook wb = null;
        boolean isExcel2003 = ExcelVersionUtil.isExcel2003(filePathOrName);

        if(isExcel2003){
            wb = new HSSFWorkbook(in);
        } else{
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    static  class ExcelVersionUtil{
        /**
         * 是否是2003的excel，返回true是2003
         */
        public static boolean isExcel2003(String filePath){
             return  filePath.matches("^.+\\.(?i)(xls)$");
        }

        /**
         * 是否是2007的excel，返回true是2007
         */
        public static  boolean isExcel2007(String filePath){

            return filePath.matches("^.+\\.(?i)(xls)$");
        }
    }
}
